set more off
clear all

cap log close
log using $DIR\cr_enia, t replace

********************************************************** 1995
import excel $ENIADIR\enia_1995.xlsx,  firstrow clear
rename *, lower
keep dv idb region imarval evtas evbp eva ciiu2 ciiu3 tamano ano forpro pornac porext jortra orgjur diatra diapar proprh proprm dirprh dirprm espprh espprm admprh admprm comprh comprm ndiprh ndiprm ninprh ninprm serprh serprm tothom totmuj rememp regemp itc tocc tohsc tomsc tosc mtmpval mimpval mrevval mconval msubval tagcan tagval comcan comval gencan vencan venval tcoval tseval edidep maqdep muedep vehdep otrdep diegas intgas divgas totiva divret cbnter cbnedi cbnmaq cbnveh cbuter cbuedi cbumaq cbuveh vbuter vbuedi vbumaq vbuveh refter refedi refmaq refveh vbnter vbnedi vbnmaq vbnveh vocter vocedi vocmaq vocveh corter coredi cormaq corveh salter saledi salmaq salveh texvap texvaf acthab pasdeb gashab ingdeb saldeb salhab costot rempag matpco mateco coagin elecomp elecons tcomco tgasete tdeprec tgasfin fabval expval revval ipatval ingtot vbp va abaf vpf ingtotal ingexp
* not available: vstk hhdiadir hhdiaind hhdiapr  thhdir thhind  thhano cfamaux  dleaux  regaux remaux  hrsaux hrsserv hrsespsub hrsadmsub  hrsmant hrsnocali hrsnocali hrsnocald hrscoms hrsadmin hrsespec hrsdirec hrsprop teed otrret nui dvn 
rename idbase id
gen enia=1995

*** aggregate at ID-level (dv plant counter)
foreach x in "ciiu2" "ciiu3" {
egen id_`x' = mode(`x'),by(id) max
}

* 1 persona natural
* 2 sociedad de responsabilidad limitada
* 3 sociedad colectiva
* 4 sociedad anonima
* 5 cooperative
* 6 publica
* 7 otra
* pick sa if sa among establishments
gen x=1 if orgjur==4
egen xx=max(x),by(id)
gen id_orgjur=4 if xx==1
drop x*
* pick srl if srl among establishments (but no sa)
gen x=1 if orgjur==2
egen xx=max(x),by(id)
replace id_orgjur=2 if xx==1 & id_org==.
drop x*
* if public and otra among establishments, pick public
gen x=1 if orgjur==6
egen xx=max(x),by(id)
replace id_orgjur=6 if xx==1 & id_org==.
drop x*
replace id_orgjur=orgjur if id_org==.


* 1 privada nacional
* 2 privada extranjera
* 3 mixta 
* 4 estatal 
* pick mixta if among establishments
gen x=1 if forpro==3
egen xx=max(x),by(id)
gen id_forpro=3 if xx==1
drop x*
* pick foreign if among establishments (but not mixta)
gen x=1 if forpro==2
egen xx=max(x),by(id)
replace id_forpro=2 if xx==1 & id_forpro==.
drop x*
* pick domestic government owned if among establishments (but not mixta/foreign)
gen x=1 if forpro==4
egen xx=max(x),by(id)
replace id_forpro=4 if xx==1 & id_forpro==.
drop x*
replace id_forpro=forpro if id_forpro==.


* create artifical multiestablishment region
* region 13 is "Región Metropolitana de Santiago"
gen x=1 if region==13
egen xx=max(x),by(id)
gen id_region=13 if xx==1
drop x*
replace id_region=16 if id_region==.


*aggregate national capital = national share*capital
*aggregate foreign capital = foreign share*capital
egen employ=rowtotal(tothom totmuj)
* correction
replace pornac=100 if pornac>100
replace porext=100 if porext>100
replace pornac=. if pornac==0 & porext==0
replace porext=. if pornac==. & porext==0
*replace employ=. if pornac==. & porext==.
*aggregate national capital = national share*size measure
gen xpornac=employ*(pornac/100)
egen xxpornac=total(xpornac),by(id)
egen xemploy=total(employ),by(id)
gen id_sharenational=xxpornac/xemploy
*aggregate foreign capital = foreign share*size measure
gen xporext=employ*(porext/100)
egen xxporext=total(xporext),by(id)
gen id_shareforeign=xxporext/xemploy
drop x* employ


local list1 "imarval tothom totmuj"
foreach x of local list1{
egen id_`x' = total(`x'),by(id)
}

local list2 "evtas evbp eva tamano jortra"
foreach x of local list2{
egen id_`x' = max(`x'),by(id)
}

local list3 "diatra diapar proprh proprm dirprh dirprm espprh espprm admprh admprm comprh comprm ndiprh ndiprm ninprh ninprm serprh serprm rememp regemp itc tocc tohsc tomsc tosc mtmpval mimpval mrevval mconval msubval tagcan tagval comcan comval gencan vencan venval tcoval tseval edidep maqdep muedep vehdep otrdep diegas intgas divgas totiva divret cbnter cbnedi cbnmaq cbnveh cbuter cbuedi cbumaq cbuveh vbuter vbuedi vbumaq vbuveh refter refedi refmaq refveh vbnter vbnedi vbnmaq vbnveh vocter vocedi vocmaq vocveh corter coredi cormaq corveh salter saledi salmaq salveh texvap texvaf acthab pasdeb gashab ingdeb saldeb salhab costot rempag matpco mateco coagin elecomp elecons tcomco tgasete tdeprec tgasfin fabval expval revval ipatval ingtot vbp va abaf vpf ingtotal ingexp"
foreach x of local list3{
egen id_`x' = total(`x'),by(id)
}

egen nestab = count(dv),by(id ano)

keep id* ano enia nestab
duplicates drop

save $ENIADIR\enia, replace



********************************************************** 1996-1999
forval i=1996(1)1999 {
import excel "$ine\enia_`i'.xlsx",  firstrow clear
rename *, lower
keep dv idb region imarval evtas evbp eva ciiu2 ciiu3 tamano ano forpro pornac porext jortra orgjur diatra diapar proprh proprm dirprh dirprm espprh espprm admprh admprm comprh comprm ndiprh ndiprm ninprh ninprm serprh serprm tothom totmuj rememp regemp itc tocc tohsc tomsc tosc mtmpval mimpval mrevval mconval msubval tagcan tagval comcan comval gencan vencan venval tcoval tseval edidep maqdep muedep vehdep otrdep diegas intgas divgas totiva divret cbnter cbnedi cbnmaq cbnveh cbuter cbuedi cbumaq cbuveh vbuter vbuedi vbumaq vbuveh refter refedi refmaq refveh vbnter vbnedi vbnmaq vbnveh vocter vocedi vocmaq vocveh corter coredi cormaq corveh salter saledi salmaq salveh texvap texvaf acthab pasdeb gashab ingdeb saldeb salhab costot rempag matpco mateco coagin elecomp elecons tcomco tgasete tdeprec tgasfin fabval expval revval ipatval ingtot vbp va abaf vpf ingtotal ingexp
* not available: vstk hhdiadir hhdiaind hhdiapr  thhdir thhind  thhano cfamaux  dleaux  regaux remaux  hrsaux hrsserv hrsespsub hrsadmsub  hrsmant hrsnocali hrsnocali hrsnocald hrscoms hrsadmin hrsespec hrsdirec hrsprop teed otrret nui dvn 
rename idbase id
gen enia=`i'

if enia==1999{
*** correct a few entries
replace vbuveh="6391" if index(vbuveh,"6391")
replace vocmaq="" if index(vocmaq,"332589135")
replace gashab="4742" if index(gashab,"4742")

destring vbuveh, replace
destring vocmaq, replace
destring gashab, replace
}


if enia==1996{
* 1 persona natural
* 2 sociedad de responsabilidad limitada
* 3 sociedad colectiva
* 4 sociedad anonima
* 5 cooperative
* 6 publica
* 7 otra
* pick sa if sa among establishments
gen x=1 if orgjur==4
egen xx=max(x),by(id)
gen id_orgjur=4 if xx==1
drop x*
* pick srl if srl among establishments (but no sa)
gen x=1 if orgjur==2
egen xx=max(x),by(id)
replace id_orgjur=2 if xx==1 & id_org==.
drop x*
* if public and otra among establishments, pick public
gen x=1 if orgjur==6
egen xx=max(x),by(id)
replace id_orgjur=6 if xx==1 & id_org==.
drop x*
replace id_orgjur=orgjur if id_org==.
}


* problem in enia==1997
replace orgjur=1 if orgjur==10
replace orgjur=2 if orgjur==20
replace orgjur=3 if orgjur==30
replace orgjur=4 if orgjur==41
replace orgjur=4 if orgjur==42
replace orgjur=5 if orgjur==50
replace orgjur=6 if orgjur==60
replace orgjur=7 if orgjur==70


if enia>1996{
* 1 persona natural
* 2 sociedad de responsabilidad limitada
* 3 sociedad colectiva
* 4 sociedad anonima cerrada
* 5 sociedad anonima abierta
* 6 cooperative
* 7 publica
* 8 otra
* pick publicly traded if among establishments
gen x=1 if orgjur==5
egen xx=max(x),by(id)
gen id_orgjur=5 if xx==1
drop x*
* pick sa if sa among establishments
gen x=1 if orgjur==4
egen xx=max(x),by(id)
replace id_orgjur=4 if xx==1 & id_org==.
drop x*
* pick srl if srl among establishments (but no sa)
gen x=1 if orgjur==2
egen xx=max(x),by(id)
replace id_orgjur=2 if xx==1 & id_org==.
drop x*
* if public and otra among establishments, pick public
gen x=1 if orgjur==7
egen xx=max(x),by(id)
replace id_orgjur=7 if xx==1 & id_org==.
drop x*
replace id_orgjur=orgjur if id_org==.
}


* 1 privada nacional
* 2 privada extranjera
* 3 mixta 
* 4 estatal 
* pick mixta if among establishments
gen x=1 if forpro==3
egen xx=max(x),by(id)
gen id_forpro=3 if xx==1
drop x*
* pick foreign if among establishments (but not mixta)
gen x=1 if forpro==2
egen xx=max(x),by(id)
replace id_forpro=2 if xx==1 & id_forpro==.
drop x*
* pick domestic government owned if among establishments (but not mixta/foreign)
gen x=1 if forpro==4
egen xx=max(x),by(id)
replace id_forpro=4 if xx==1 & id_forpro==.
drop x*
replace id_forpro=forpro if id_forpro==.


* create artifical multiestablishment region
* region 13 is "Región Metropolitana de Santiago"
gen x=1 if region==13
egen xx=max(x),by(id)
gen id_region=13 if xx==1
drop x*
replace id_region=16 if id_region==.


*aggregate national capital = national share*capital
*aggregate foreign capital = foreign share*capital
egen employ=rowtotal(tothom totmuj)
* correction
replace pornac=100 if pornac>100
replace porext=100 if porext>100
replace pornac=. if pornac==0 & porext==0
replace porext=. if pornac==. & porext==0
*replace employ=. if pornac==. & porext==.
*aggregate national capital = national share*size measure
gen xpornac=employ*(pornac/100)
egen xxpornac=total(xpornac),by(id)
egen xemploy=total(employ),by(id)
gen id_sharenational=xxpornac/xemploy
*aggregate foreign capital = foreign share*size measure
gen xporext=employ*(porext/100)
egen xxporext=total(xporext),by(id)
gen id_shareforeign=xxporext/xemploy
drop x* employ


*** aggregate at ID-level (dv plant counter)
foreach x in "ciiu2" "ciiu3" {
egen id_`x' = mode(`x'),by(id ano) max
}


local list1 "imarval tothom totmuj"
foreach x of local list1{
egen id_`x' = total(`x'),by(id)
}

local list2 "evtas evbp eva tamano jortra"
foreach x of local list2{
egen id_`x' = max(`x'),by(id)
}

local list3 "diatra diapar proprh proprm dirprh dirprm espprh espprm admprh admprm comprh comprm ndiprh ndiprm ninprh ninprm serprh serprm rememp regemp itc tocc tohsc tomsc tosc mtmpval mimpval mrevval mconval msubval tagcan tagval comcan comval gencan vencan venval tcoval tseval edidep maqdep muedep vehdep otrdep diegas intgas divgas totiva divret cbnter cbnedi cbnmaq cbnveh cbuter cbuedi cbumaq cbuveh vbuter vbuedi vbumaq vbuveh refter refedi refmaq refveh vbnter vbnedi vbnmaq vbnveh vocter vocedi vocmaq vocveh corter coredi cormaq corveh salter saledi salmaq salveh texvap texvaf acthab pasdeb gashab ingdeb saldeb salhab costot rempag matpco mateco coagin elecomp elecons tcomco tgasete tdeprec tgasfin fabval expval revval ipatval ingtot vbp va abaf vpf ingtotal ingexp"
foreach x of local list3{
egen id_`x' = total(`x'),by(id)
}

egen nestab = count(dv),by(id ano)

keep id* ano enia nestab
duplicates drop

append using $ENIADIR\enia

save $ENIADIR\enia, replace
}

set more off
********************************************************** 2000-2005
forval i=2000(1)2005 {
import excel "$ine\enia_`i'.xlsx",  firstrow clear
rename *, lower
keep dv vstk hhdiadir hhdiaind hhdiapr  thhdir thhind  thhano cfamaux  dleaux  regaux remaux  hrsaux hrsserv hrsespsub hrsadmsub  hrsmant hrsnocali hrsnocali hrsnocald hrscoms hrsadmin hrsespec hrsdirec hrsprop teed idb region imarval evtas evbp eva ciiu2 ciiu3 tamano ano forpro pornac porext jortra orgjur diatra diapar proprh proprm dirprh dirprm espprh espprm admprh admprm comprh comprm ndiprh ndiprm ninprh ninprm serprh serprm tothom totmuj rememp regemp itc tocc tohsc tomsc tosc mtmpval mimpval mrevval mconval msubval tagcan tagval comcan comval gencan vencan venval tcoval tseval edidep maqdep muedep vehdep otrdep diegas intgas divgas totiva divret cbnter cbnedi cbnmaq cbnveh cbuter cbuedi cbumaq cbuveh vbuter vbuedi vbumaq vbuveh refter refedi refmaq refveh vbnter vbnedi vbnmaq vbnveh vocter vocedi vocmaq vocveh corter coredi cormaq corveh salter saledi salmaq salveh texvap texvaf acthab pasdeb gashab ingdeb saldeb salhab costot rempag matpco mateco coagin elecomp elecons tcomco tgasete tdeprec tgasfin fabval expval revval ipatval ingtot vbp va abaf vpf ingtotal ingexp
* not available: otrret nui dvn 
rename idbase id
gen enia=`i'

* 1 persona natural
* 2 sociedad de responsabilidad limitada
* 3 sociedad colectiva
* 4 sociedad anonima cerrada
* 5 sociedad anonima abierta
* 6 cooperative
* 7 publica
* 8 otra
* pick publicly traded if among establishments
gen x=1 if orgjur==5
egen xx=max(x),by(id)
gen id_orgjur=5 if xx==1
drop x*
* pick sa if sa among establishments
gen x=1 if orgjur==4
egen xx=max(x),by(id)
replace id_orgjur=4 if xx==1 & id_org==.
drop x*
* pick srl if srl among establishments (but no sa)
gen x=1 if orgjur==2
egen xx=max(x),by(id)
replace id_orgjur=2 if xx==1 & id_org==.
drop x*
* if public and otra among establishments, pick public
gen x=1 if orgjur==7
egen xx=max(x),by(id)
replace id_orgjur=7 if xx==1 & id_org==.
drop x*
replace id_orgjur=orgjur if id_org==.


* 1 privada nacional
* 2 privada extranjera
* 3 mixta 
* 4 estatal 
* pick mixta if among establishments
gen x=1 if forpro==3
egen xx=max(x),by(id)
gen id_forpro=3 if xx==1
drop x*
* pick foreign if among establishments (but not mixta)
gen x=1 if forpro==2
egen xx=max(x),by(id)
replace id_forpro=2 if xx==1 & id_forpro==.
drop x*
* pick domestic government owned if among establishments (but not mixta/foreign)
gen x=1 if forpro==4
egen xx=max(x),by(id)
replace id_forpro=4 if xx==1 & id_forpro==.
drop x*
replace id_forpro=forpro if id_forpro==.


* create artifical multiestablishment region
* region 13 is "Región Metropolitana de Santiago"
gen x=1 if region==13
egen xx=max(x),by(id)
gen id_region=13 if xx==1
drop x*
replace id_region=16 if id_region==.

*aggregate national capital = national share*capital
*aggregate foreign capital = foreign share*capital
egen employ=rowtotal(tothom totmuj)
* correction
replace pornac=100 if pornac>100
replace porext=100 if porext>100
replace pornac=. if pornac==0 & porext==0
replace porext=. if pornac==. & porext==0
*replace employ=. if pornac==. & porext==.
*aggregate national capital = national share*size measure
gen xpornac=employ*(pornac/100)
egen xxpornac=total(xpornac),by(id)
egen xemploy=total(employ),by(id)
gen id_sharenational=xxpornac/xemploy
*aggregate foreign capital = foreign share*size measure
gen xporext=employ*(porext/100)
egen xxporext=total(xporext),by(id)
gen id_shareforeign=xxporext/xemploy
drop x* employ

*** aggregate at ID-level (dv plant counter)
foreach x in "ciiu2" "ciiu3" {
egen id_`x' = mode(`x'),by(id ano) max
}


*** variables available only for 2000-2005
local list1 "hhdiadir hhdiaind hhdiapr thhdir thhind thhano cfamaux regaux dleaux remaux hrsaux hrsserv hrsespsub hrsadmsub hrsmant hrsnocali hrsnocald hrscoms hrsadmin hrsespec hrsdirec hrsprop"
foreach x of local list1{
egen id_`x' = total(`x'),by(id)
}

foreach x in "teed"{
egen id_`x' = max(`x'),by(id)
}

*** 1995-2005
local list1 "imarval tothom totmuj"
foreach x of local list1{
egen id_`x' = total(`x'),by(id)
}

local list2 "evtas evbp eva tamano jortra"
foreach x of local list2{
egen id_`x' = max(`x'),by(id)
}

local list3 "diatra diapar proprh proprm dirprh dirprm espprh espprm admprh admprm comprh comprm ndiprh ndiprm ninprh ninprm serprh serprm rememp regemp itc tocc tohsc tomsc tosc mtmpval mimpval mrevval mconval msubval tagcan tagval comcan comval gencan vencan venval tcoval tseval edidep maqdep muedep vehdep otrdep diegas intgas divgas totiva divret cbnter cbnedi cbnmaq cbnveh cbuter cbuedi cbumaq cbuveh vbuter vbuedi vbumaq vbuveh refter refedi refmaq refveh vbnter vbnedi vbnmaq vbnveh vocter vocedi vocmaq vocveh corter coredi cormaq corveh salter saledi salmaq salveh texvap texvaf acthab pasdeb gashab ingdeb saldeb salhab costot rempag matpco mateco coagin elecomp elecons tcomco tgasete tdeprec tgasfin fabval expval revval ipatval ingtot vbp va abaf vpf ingtotal ingexp vstk"
foreach x of local list3{
egen id_`x' = total(`x'),by(id)
}

egen nestab = count(dv),by(id ano)

keep id* ano enia nestab
duplicates drop

append using "$ine\enia"

save $ENIADIR\enia, replace
}

do $DIR\cleanfile
save $ENIADIR\enia, replace
sum
tab ano

log close
